﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SmoLiteApi.Sql;
using System.Data.SqlClient;
using SmoLiteApi.Helper;

namespace SmoLiteApi
{
    /// <summary/>
    public class Table
    {
        private Database _database;
        private string _name;
        private string _schemaName;
        private ColumnCollection _columnCollection;
        private ForeignKeyCollection _foreignKeyCollection;
        private IndexCollection _indexCollection;

        /// <summary/>
        public Table(
            Database database,
            string name,
            string schemaName)
        {
            this._database = database;
            this._name = name;
            this._schemaName = schemaName;
            this._columnCollection = new ColumnCollection();
            this._indexCollection = new IndexCollection();
            this._foreignKeyCollection = new ForeignKeyCollection();
        }

        /// <summary/>
        public string Name
        {
            get { return this._name; }
            set { this._name = value; }
        }

        /// <summary/>
        public ColumnCollection Columns
        {
            get { return this._columnCollection; }
        }

        /// <summary/>
        public IndexCollection Indexes
        {
            get { return this._indexCollection; }
        }

        /// <summary/>
        public ForeignKeyCollection ForeignKeys
        {
            get { return this._foreignKeyCollection; }
        }

        /// <summary/>
        public string Schema
        {
            get { return this._schemaName; }
        }

        /// <summary/>
        public void Create()
        {
            TableCreateSql tableCreateSql = new TableCreateSql();

            // set attributes
            tableCreateSql.AddObject(new { Name = this._name, Schema = this._schemaName, ColumnCollection = this._columnCollection });

            using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, tableCreateSql.Render()))
            {
                // run the command
                sqlCommandHelper.ExecuteNonQuery();
            }

            foreach (Index index in this._indexCollection)
            {
                CommonIndexCreateSql indexCreateSql = new CommonIndexCreateSql();

                // set attributes
                indexCreateSql.AddObject(new { Table = this._name, Schema = this._schemaName });
                indexCreateSql.AddObject(index);

                using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, indexCreateSql.Render()))
                {
                    // run the command
                    sqlCommandHelper.ExecuteNonQuery();
                }
            }

            foreach (ForeignKey foreignKey in this._foreignKeyCollection)
            {
                ForeignKeyCreateSql foreignKeyCreateSql = new ForeignKeyCreateSql();

                // set attributes
                foreignKeyCreateSql.AddObject(new { Table = this._name, Schema = this._schemaName });
                foreignKeyCreateSql.AddObject(foreignKey);

                using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, foreignKeyCreateSql.Render()))
                {
                    // run the command
                    sqlCommandHelper.ExecuteNonQuery();
                }
            }
        }

        /// <summary/>
        public void Update()
        {
            // at present the routine only rebuilds or creates indexes ... dropping indexes is a bit of
            // a problem since it could be referenced by a foreign key (maybe) and because the rules differ
            // for certain indexes such as unique indexes ... manipulating columns or foriegn keys 
            // can be tricky because of dependencies on other tables and data manipluation needed 
            // due to structural changes in the table ... alter indexes is also not supported

            // fetch the current table
            Table currentTable = this._database.Tables[this._name, this._schemaName];
            
            foreach (Index index in this._indexCollection)
            {
                // does the index exist
                if (currentTable._indexCollection[index.Name] != null)
                {
                    CommonIndexRebuildSql indexRebuildSql = new CommonIndexRebuildSql();

                    // set attributes
                    indexRebuildSql.AddObject(new { Table = this._name, Schema = this._schemaName });
                    indexRebuildSql.AddObject(index);

                    using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, indexRebuildSql.Render()))
                    {
                        // run the command
                        sqlCommandHelper.ExecuteNonQuery();
                    }
                }
                else
                {
                    CommonIndexCreateSql indexCreateSql = new CommonIndexCreateSql();

                    // set attributes
                    indexCreateSql.AddObject(new { Table = this._name, Schema = this._schemaName });
                    indexCreateSql.AddObject(index);

                    using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, indexCreateSql.Render()))
                    {
                        // run the command
                        sqlCommandHelper.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}
